# -*- coding:utf-8 -*-

import json
import os
import re
from os import path

import pymysql


# 生成建表DDL
def get_table_ddl(p_tbname, p_col_list):
    r_sql = "CREATE TABLE  IF NOT EXISTS "
    r_sql = r_sql + p_tbname
    r_sql = r_sql + "(" + " varchar(400) ,".join(p_col_list) + " varchar(400)" + ")"
    return r_sql


def create_table(ddl):
    db = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='1234', db='test')
    cursor = db.cursor()
    cursor.execute(ddl)
    db.commit()
    return 'SUCCESS!'


# 数据导入，需要定义插入的SQL
def datas2mysql(p_datas, p_col_list, p_tbname):
    db = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='1234', db='test')
    cursor = db.cursor()
    cols = ",".join(p_col_list)
    vals = "%s," * len(p_col_list)
    res_sql = "insert into {}({}) values({})".format(p_tbname, cols, vals[:-1])
    cursor.executemany(res_sql, p_datas)
    db.commit()


if __name__ == '__main__':
    file_dir = r'D:\工作文件\宁波鄞州项目\task'
    file_list = os.listdir(file_dir)
    # print("file_list列表 =", file_list)
    for item in file_list:
        print(item)
        file_path = path.join(file_dir, item)
        with open(file_path, encoding='utf-8') as f:
            line = f.readline()
            # print(line)
            # 将布尔值替换为字符串
            new_data = line.replace("true", "True")
            new_data = eval(new_data)
            # 解析为JSON字符串
            json_str = json.dumps(new_data)
            json_data = json.loads(json_str)
            # 需要的项为JSON的data节点
            json_dict = json_data['data']
            # print(json_dict)
            # 字段列表
            col_list_str = json_dict.get('header')
            col_list = json.loads(col_list_str)
            # 查询用的SQL
            sql = json_dict.get('sql')
            print(sql)
            # 获取表名
            table_name = re.findall("nb_dw\.(.*?)\)", sql)[0]
            print(table_name)
            # 建立空表
            tb_ddl = get_table_ddl(table_name, col_list)
            print(tb_ddl)
            create_table(tb_ddl)
            # 获取记录数
            cnt = json_dict.get('count')
            # 0条记录直接略过
            if cnt < 1:
                continue
            # 数据项为body节点
            data_body = json_dict.get('body')
            list_data = json.loads(data_body)
            datas2mysql(list_data, col_list, table_name)
            f.close()
    print('SUCCESS!')
